package com.zzgc.modules.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.zzgc.common.entity.StudentArchives;
import com.zzgc.common.entity.SysFile;
import com.zzgc.common.response.HttpStatus;
import com.zzgc.common.response.Result;
import com.zzgc.modules.param.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.models.auth.In;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;

/**
 * 下载管理
 *
 * @Author:
 * @CreateTime: 2022-12-07  10:59
 * @Description:
 * @Version: 1.0
 */
@RestController
@RequestMapping("download")
@Api(tags = "下载管理")
public class DownloadController extends BaseController {

    @GetMapping("getRecords")
    @ApiOperation("获取下载记录")
    public Result getSchoolList(String sYear, Integer sNumber, Integer page, Integer size) {
        SysFile sysFile = new SysFile();
        sysFile.setSSchoolId(Long.valueOf(getSchoolId()));
        sysFile.setSYear(sYear);
        sysFile.setSNumber(sNumber);
        sysFile.setPage(page);
        sysFile.setSize(size);
        List<SysFile> sysFiles = sysFileService.queryByPage(sysFile);
        HashMap<String, Object> map = new HashMap<>();
        map.put("total", sysFiles.size() == 0 ? 0 : sysFiles.get(0).getTotal());
        map.put("data", sysFiles);
        return Result.builder()
                .code(HttpStatus.OK.value())
                .msg(HttpStatus.OK.getReasonPhrase())
                .data(map)
                .build();
    }

    // 自己用的导出档案信息
    @GetMapping("export")
    @ApiOperation(value = "导出学校档案信息", notes = "年份 : sYear, 检测次数: sNumber, 导出类型: type 1 普通导出, 0是脱敏导出")
    public void downloadAllArchives(String sYear, Integer sNumber, Integer type) throws IOException {
        StudentArchives archives = new StudentArchives();
        archives.setTableName(super.getStudentTable());
        if (sYear == null) {
            archives.setSYear(String.valueOf(getsYear()));
        } else {
            archives.setSYear(sYear);
        }
        // 默认数据库现有数据的最后一次
        archives.setSNumber(sNumber);
        archives.setSSchoolId(Long.valueOf(super.getSchoolId()));
        // 获取学校数据
        List<ExportArchivesTemplate> exportArchivesTemplates = studentArchivesService.queryAllByLimitExport(archives);
        // 对年级和班级进行排序
        Collections.sort(exportArchivesTemplates, new ExportArchivesTemplate());
        if (type != null && type == 1) {
            for (ExportArchivesTemplate exportArchivesTemplate : exportArchivesTemplates) {
                List<StudentParamTemplate> students = exportArchivesTemplate.getStudents();
                List<StudentParamTemplate> studentEdit = new ArrayList<>();
                // 给每个学生增加一行数据, 方便左右眼导出
                for (StudentParamTemplate studentR : students) {
                    studentR.setEye("右眼");
                    StudentParamTemplate studentL = studentR.clone();
                    studentEdit.add(studentR);
                    studentL.setEye("左眼");
                    studentEdit.add(studentL);
                }
                exportArchivesTemplate.setStudents(studentEdit);
            }
        } else {
            for (ExportArchivesTemplate exportArchivesTemplate : exportArchivesTemplates) {
                List<StudentParamTemplate> students = exportArchivesTemplate.getStudents();
                List<StudentParamTemplate> studentEdit = new ArrayList<>();
                // 给每个学生增加一行数据, 方便左右眼导出
                for (StudentParamTemplate studentR : students) {
                    studentR.setName(studentR.getName().substring(0, 1) + "**");
                    studentR.setEye("右眼");
                    StudentParamTemplate studentL = studentR.clone();
                    studentEdit.add(studentR);
                    studentL.setEye("左眼");
                    studentEdit.add(studentL);
                }
                exportArchivesTemplate.setStudents(studentEdit);
            }
        }
        super.response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        super.response.setCharacterEncoding("utf-8");
        String fileName1 = "";
        String schoolName = "";
        if (exportArchivesTemplates.size() != 0 && exportArchivesTemplates.get(0).getSchoolName() != null) {
            schoolName = exportArchivesTemplates.get(0).getSchoolName() + "视力分析";
            fileName1 = URLEncoder.encode(schoolName, "UTF-8").replaceAll("\\+", "%20");
        } else {
            throw new RuntimeException("暂无数据!");
            //schoolName = "学生档案视力分析表";
           // fileName1 = URLEncoder.encode(schoolName, "UTF-8").replaceAll("\\+", "%20");
        }
        super.response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName1 + ".xlsx");

        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // {} 代表普通变量 {.} 代表是list的变量
        String templateFileName = Thread.currentThread().getContextClassLoader().getResource("").getPath() + "templates/Template.xlsx";
        //String fileName = "E:\\桌面\\test1.xlsx";
        // 随机生成文件名
        String fileN = UUID.randomUUID().toString().replace("-", "") + ".xlsx";
        String fileName = downloadFilePath + fileN;
        File file = new File(downloadFilePath);
        if (!file.exists()){
            file.mkdirs();
        }
        // 方法2 自定义合并单元格策略
        // 每隔2行会合并 把eachColumn 设置成 3 也就是我们数据的长度，所以就第一列会合并。当然其他合并策略也可以自己写
        LoopMergeStrategy loopMergeStrategy0 = new LoopMergeStrategy(2, 0); //第0列合并
        LoopMergeStrategy loopMergeStrategy1 = new LoopMergeStrategy(2, 1); // 第1列合并
        LoopMergeStrategy loopMergeStrategy9 = new LoopMergeStrategy(2, 9); // 第9列合并
        LoopMergeStrategy loopMergeStrategy10 = new LoopMergeStrategy(2, 10); // 第10列合并
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        //读取模板文件

        InputStream inputStream = new FileInputStream(templateFileName);
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        workbook.setSheetName(0, exportArchivesTemplates.get(0).getGradeName() + "-" + exportArchivesTemplates.get(0).getClassName());
//        if (!exportParam.isMultiExcelExportFlag()){
        for (int i = 1; i < exportArchivesTemplates.size(); i++) {
            //复制模板，得到第i个sheet
            workbook.cloneSheet(0, exportArchivesTemplates.get(i).getGradeName() + "-" + exportArchivesTemplates.get(i).getClassName());
        }
//        }
        //写到流里
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        workbook.write(bos);
        byte[] bArray = bos.toByteArray();
        inputStream = new ByteArrayInputStream(bArray);
        //数据填充
        ExcelWriter excelWriter = null;
        try {
//            excelWriter = EasyExcel.write(super.response.getOutputStream())
            excelWriter = EasyExcel.write(fileName)
                    .registerWriteHandler(loopMergeStrategy0)
                    .registerWriteHandler(loopMergeStrategy1)
                    .registerWriteHandler(loopMergeStrategy9)
                    .registerWriteHandler(loopMergeStrategy10)
                    .withTemplate(inputStream).build();
            for (int i = 0; i < exportArchivesTemplates.size(); i++) {
                ExportArchivesTemplate archivesTemplate = exportArchivesTemplates.get(i);
                WriteSheet writeSheet = EasyExcel.writerSheet(archivesTemplate.getGradeName() + "-" + archivesTemplate.getClassName()).build();
                excelWriter.fill(archivesTemplate.getStudents(), writeSheet);
                excelWriter.fill(archivesTemplate, writeSheet);
            }
        } finally {
            // 千万别忘记关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        }
        SysFile sysFile = new SysFile();
        sysFile.setSSchoolId(archives.getSSchoolId());
        sysFile.setSYear(archives.getSYear());
        sysFile.setSNumber(sNumber);
        sysFile.setSFileName(schoolName);
        //1 普通导出, 0是脱敏导出
        sysFile.setSFileType(type == 0 ? "脱敏数据" : "普通数据");
        sysFile.setSFileUrl("excel/" + fileN);
        sysFile.setSCreateDate(new Date());
        sysFile.setSCreateBy(super.getUserName());
        sysFileService.insert(sysFile);
        // 传输文件
        FileInputStream fis = new FileInputStream(fileName);
        ServletOutputStream outputStream = response.getOutputStream();
        byte[] b = new byte[2048];
        int i = 0;
        while ((i = fis.read(b)) != -1) {
            outputStream.write(b, 0, i);
        }
        fis.close();
    }

    // 对接中国学生体质健康平台版
    @GetMapping("mainExport")
    @ApiOperation(value = "导出学校档案信息(对接中国学生体质健康平台版)", notes = "年份 : sYear, 检测次数: sNumber")
    public void mainExport(String sYear, Integer sNumber) throws IOException {
        if (super.getUserRole() != null && !super.getUserRole().equals("5")) {
            throw new RuntimeException("权限不足,您不是学校管理员!");
        }
        // 查询学生档案列表
        StudentArchives archives = new StudentArchives();
        archives.setTableName(super.getStudentTable());
        if (sYear == null) {
            archives.setSYear(String.valueOf(getsYear()));
        } else {
            archives.setSYear(sYear);
        }
        // 默认数据库现有数据的最后一次
        archives.setSNumber(sNumber);
        archives.setSSchoolId(Long.valueOf(super.getSchoolId()));
        // 获取学校数据
        List<ExportArchivesTemplate> exportArchivesTemplates = studentArchivesService.queryAllByLimitExport(archives);
        // 对年级和班级进行排序
        Collections.sort(exportArchivesTemplates, new ExportArchivesTemplate());
        // 设置响应头
        super.response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        super.response.setCharacterEncoding("utf-8");
        String fileName = "";
        if (exportArchivesTemplates.size() != 0 && exportArchivesTemplates.get(0).getSchoolName() != null) {
            fileName = URLEncoder.encode(exportArchivesTemplates.get(0).getSchoolName() + "视力分析", "UTF-8").replaceAll("\\+", "%20");
        } else {
            throw new RuntimeException("暂无数据!");
        }
//        String fileName = URLEncoder.encode(fileName1, "UTF-8").replaceAll("\\+", "%20");
        super.response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        ExcelWriter excelWriter = null;
        try{
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 背景设置为白色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
            WriteFont headWriteFont = new WriteFont();
            // 字体大小
            headWriteFont.setFontHeightInPoints((short)12);
            headWriteFont.setFontName("宋体");
            // 是否加粗
            headWriteFont.setBold(false);
            headWriteCellStyle.setWriteFont(headWriteFont); // 设置进头

//            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 水平对齐
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 垂直对齐
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            WriteFont writeFont = new WriteFont();
            // 字体
            writeFont.setFontName("宋体");
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setWriteFont(writeFont);
//            // 字体大小
//            contentWriteFont.setFontHeightInPoints((short)20);
//            contentWriteCellStyle.setWriteFont(contentWriteFont);
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            // 生成excel表格
            excelWriter = EasyExcel.write(super.response.getOutputStream()) // 导出的文件流
                    .head(MainExportTemplate.class) // 导出类结构
                    .registerWriteHandler(horizontalCellStyleStrategy) // 设置内容
                    .excelType(ExcelTypeEnum.XLSX) // 导出类型
                    .build();
            for (ExportArchivesTemplate exportArchivesTemplate : exportArchivesTemplates) {
                List<StudentParamTemplate> studentList = exportArchivesTemplate.getStudents();
                List<MainExportTemplate> data = new ArrayList<>();
                for (StudentParamTemplate studentParamTemplate : studentList) {
                    MainExportTemplate template = new MainExportTemplate();
                    template.setName(studentParamTemplate.getName());
                    // 采用五分记录法, 保留一位小数, 录入范围 3.0-5.3之间, 低于3.0的以"9" 代替
                    String rightEyeVisual = studentParamTemplate.getRightEyeVisual();
                    if (rightEyeVisual != null) {
                        float v = Float.parseFloat(rightEyeVisual);
                        if (v < 3.0){
                            template.setRightEyeVisual("9");
                        }else {
                            template.setRightEyeVisual(rightEyeVisual);
                        }
                    }
                    String leftEyeVisual = studentParamTemplate.getLeftEyeVisual();
                    if (leftEyeVisual != null){
                        float v = Float.parseFloat(leftEyeVisual);
                        if (v < 3.0){
                            template.setLeftEyeVisual("9");
                        }else {
                            template.setLeftEyeVisual(leftEyeVisual);
                        }
                    }
                    // 保留2位小数, 轴位为整数,球镜柱镜数据范围-20.00D至+20.00D, 轴位0-180 ,
                    template.setRightAxial(studentParamTemplate.getRightAxial() == null ? "0" : studentParamTemplate.getRightAxial());
                    template.setRightMirror(studentParamTemplate.getRightMirror() == null ? "0" : studentParamTemplate.getRightMirror());
                    template.setColumnRight(studentParamTemplate.getColumnRight() == null ? "0" : studentParamTemplate.getColumnRight());
                    template.setLeftAxial(studentParamTemplate.getLeftAxial() == null ? "0" : studentParamTemplate.getLeftAxial());
                    template.setLeftMirror(studentParamTemplate.getLeftMirror() == null ? "0" : studentParamTemplate.getLeftMirror());
                    template.setColumnLeft(studentParamTemplate.getColumnLeft() == null ? "0" : studentParamTemplate.getColumnLeft());
                    template.setOkMirror(studentParamTemplate.getOkMirror());
                    data.add(template);
                }
                // 创建工作表
                WriteSheet writeSheet = EasyExcel.writerSheet(exportArchivesTemplate.getGradeName() + "-" + exportArchivesTemplate.getClassName()).build();
                // 写入文数据, 记得finish释放文件
                excelWriter.write(data, writeSheet);
            }
        } finally {
            // 千万别忘记关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

    }

}
